from sql import sqlseverDB
from tool import *
from config import *
import requests,json,string

ms = sqlseverDB('{SQL Server}','.','SuMaiTongPol',SQL_USER,SQL_PWD)

comman_kw_set={'t-shirt','tshirt','tee','shirt','t'}

proxies={
    'http':'http://127.0.0.1:7890',
    'https':'http://127.0.0.1:7890'
}
def req_kw(yskw,kw,ys_kw_num):
# URL 和参数
    url = "https://shopee.ph/api/v4/search/search_hint"
    headers={
        #'cookie':r'_med=refer; SPC_SC_SA_TK=; SPC_SC_SA_UD=; SPC_SC_OFFLINE_TOKEN=; SC_SSO=-; SC_SSO_U=-; fulfillment-language=en; AMP_TOKEN=%24NOT_FOUND; AC_CERT_D=U2FsdGVkX18uQZU+xWku5VPaHyaKTGTTLyxZ47jBWC5QdFDx26tUePZ4mhbd5khIGkpyPRzjGUNkDpODgZkGSmqC4wPjq/gkv5eHgkbIGdILQ2l/I0qPlla8lNmUox0Iq/AYKK9WhWNYhZHH8f7Ds35pZY89ayQ+mqXz/oXzlmG34SC5TVVaxGh0lmQQE39xdd/nCQ70jeIsDCr3m9nlmoE5EsrXG8eGLPU4gErUdx7twattgrCHyGVWq3hVjXY8b6TqCQUCQOw6Tba6m46SBnsmkVN1SpRKi3KgJ3Rvq556GoBiUEarQ6J1iKHhvrHH3Ua+qiqpguLQ6SW55PG5AV7Sx3f1r9P6sypAQXF3QFKNCMo2Xes5obIzPrGTD1cVf9N3+ZIK8f/s+mUcA95z/yS5tgESXQUi9Ptz1F4UqjgfXzp4QnaneILEJPbsHrspqmOxGWO7ozz9eDspe/S9hAkQ+hkb3xyVbRMF+h8hiaIB4psNkPDQP3UIfFmH2nTfM8230oO3itEzgNrxmbfkZFF+pqpVHRVXWrUjPiUz4MVb4lB2Gf0sxphrDvF0S7oue/+mTeqVtVS5bir2wyZsxz6l7fcGmrUOa9QVxd0jRVSqbKilPtauD8IlNcXTyUOJ+24P5tQHLDNNSlQfwOAzYUnfCCw8kqpcDEFypt0Jgg3zWxEMYdVITBKvRojZBEBQ3/cNoSRlgVVIM/sCgKOFWLIA8xZ35CTC336gVhQVVIMRiNx114anKA2PF9Sbt+jCsf4XQ7WRNmRYlo9h2yKraRHTuINh7taHO+C5Pw9PA39I3Gvlv1UrTdMyGIr572wacBy24qyPn+WCX/imOiWZAVXacWrxqEXuV9azv4hGNG6GN7V56lqJ0OC1Wp6qmo/n58XLQxl08FWCwBmCfVWV5ZZcYMc1nVW1G92ClGQuQa5d8bRaidHnhE38/zQbuJFeN6w/90+CBi9EDsNPu7WD2UqlBMTuIOOLyeD0ft+G3HcGm3l6Le1qfFqDA9JAFbWCAsSdhmq/NC3AwOk42HzR5DAJQZL1IzLlTD8pSpSa3E85oaskRyU8HG0CjSg7fr/IRKdNveDG024CYxTIwQzx2g==; web-seller-affiliate-region=id; MOCK_TYPE=; language=en; web-seller-affiliate_language=en-id; _med=refer; session_id=WEdRb1lieFBXbXJrRm9zeGxBWkRIeWd0WGdTVWZvWEM; _ga_QMX630BLLS=GS1.1.1724825382.3.1.1724829173.54.0.0; _dc_gtm_UA-61904553-8=1; _ga_8TJ45E514C=GS1.1.1725491680.3.0.1725491680.60.0.0; SPC_ST=.NDJBVUlCc2VDZTVJZDRKc9svkvOK5FkRfV2qOtwN+mWomqie6wk7QYxbp1NrN7PYUPB+L8Q3o85IN2Fl+mTAZCUv8nimHNe8dBNOJ6IYWX50VQBTtJMVStWKiC69NH/+qtRkFAxHYPX/6NLboMPtPVBua4XgR2Lfp75rnDQTeGgQRUAcxL5GzbPgZMwP9aHUffPSS5nHmBqDqp3fPNeKiPPn9sPlv+v0eXfRR0QqwfKWnA+sRN3OI1YUX1IYSqGF; SPC_U=1334490526; SPC_EC=.NDJBVUlCc2VDZTVJZDRKc9svkvOK5FkRfV2qOtwN+mWomqie6wk7QYxbp1NrN7PYUPB+L8Q3o85IN2Fl+mTAZCUv8nimHNe8dBNOJ6IYWX50VQBTtJMVStWKiC69NH/+qtRkFAxHYPX/6NLboMPtPVBua4XgR2Lfp75rnDQTeGgQRUAcxL5GzbPgZMwP9aHUffPSS5nHmBqDqp3fPNeKiPPn9sPlv+v0eXfRR0QqwfKWnA+sRN3OI1YUX1IYSqGF; SPC_R_T_ID=ETbU9ZQZ8h7RcXmD9AdQjwMrfDfzWookU8HSN7yd8ejAX7/pMQRROd/ZbT3cJ2cgViJUe6KF/GnjbFuIRwdQ9AblNf6LrYQcbuVavrHTbvbBcRS4KEIL2J7112ShfI1VcGTtejBIQFsyg/unGyei1wao5TayM88WvqxSOpDMbFI=; SPC_R_T_IV=REVwaTdoRDUyaFJETmtlUA==; SPC_T_ID=ETbU9ZQZ8h7RcXmD9AdQjwMrfDfzWookU8HSN7yd8ejAX7/pMQRROd/ZbT3cJ2cgViJUe6KF/GnjbFuIRwdQ9AblNf6LrYQcbuVavrHTbvbBcRS4KEIL2J7112ShfI1VcGTtejBIQFsyg/unGyei1wao5TayM88WvqxSOpDMbFI=; SPC_T_IV=REVwaTdoRDUyaFJETmtlUA==; SPC_SI=pmWfZgAAAABucjFjUlRCeAgkpgMAAAAAd0Y1d29meHA=; _ga_3FHJ3W4FY1=GS1.1.1725958953.2.0.1725958953.60.0.0; _gcl_au=1.1.1427441795.1728722880; _gid=GA1.3.610102962.1728722881; _ga=GA1.1.1065517213.1728722881; SPC_SC_UD=1334490526; _fbp=fb.2.1728869793232.437977169634991893; SPC_R_T_IV=dmV3Q2lpVGF1cWtoOVYwVw==; SPC_T_ID=Bq8cggAo3QPZBU4oNt/WnqdUE0R/Szj4125UV3tv1FmMHJVynXRC6mt949XfoVAsfT+QklwNxqS1AOgBob6rvG56474pzD6kKkLNzrBzVcozQYsWhQi0FRCszs0kMhb0G/v3YowhZiJXXu2GETWrCxMvq2x/DqfZxv0rlgf3ZRA=; SPC_T_IV=dmV3Q2lpVGF1cWtoOVYwVw==; SPC_F=9G1YxtoBMle3mmivLB2XTm0WDizHp3Hj; REC_T_ID=c04a1265-89cc-11ef-b566-160fb4ec64d3; SPC_R_T_ID=Bq8cggAo3QPZBU4oNt/WnqdUE0R/Szj4125UV3tv1FmMHJVynXRC6mt949XfoVAsfT+QklwNxqS1AOgBob6rvG56474pzD6kKkLNzrBzVcozQYsWhQi0FRCszs0kMhb0G/v3YowhZiJXXu2GETWrCxMvq2x/DqfZxv0rlgf3ZRA=; SPC_CLIENTID=OUcxWXh0b0JNbGUzwscnprxigvgtpldt; SPC_EC=.T3Z4Q3ZpaWIyTGwydjR6VesTd/viXlkk81QVgVygNFtfy92ryA0VPrSDEAbajs4h4k30XBv81asNeM8tJBNe+kVbIutPrgI6yGq7ipmQfAVc4z4FUIceFNYWCjQIh8x9kLl9t7uIW2fI8CzphR4/BW1uR+2dY15LJMgOYnV56ZPeIF9qVRkLJmu5SB6I9EdY/2DwCFBoI093ngUCFWSdxr/3tpY1lmYDWzLQRF+61f0g038kr4aojpnP77v7ds40; SPC_ST=.T3Z4Q3ZpaWIyTGwydjR6VesTd/viXlkk81QVgVygNFtfy92ryA0VPrSDEAbajs4h4k30XBv81asNeM8tJBNe+kVbIutPrgI6yGq7ipmQfAVc4z4FUIceFNYWCjQIh8x9kLl9t7uIW2fI8CzphR4/BW1uR+2dY15LJMgOYnV56ZPeIF9qVRkLJmu5SB6I9EdY/2DwCFBoI093ngUCFWSdxr/3tpY1lmYDWzLQRF+61f0g038kr4aojpnP77v7ds40; SPC_SC_TK=363399ba22827b95f9125936999e1f46; SPC_SC_SESSION=3a75e8c16c2e645f131f6daa17c99bc8_1_1334490526; SPC_STK=5OGi98Rtun1Etn+CnicNQKKALlULO953IuJ45QwnozPKCaM8A1pHyxvelNfc1URf3HDx96ZK9u7SjTUYu7QPeSk3PXH9LJXiNlVGB8Vz7yBogrUzBDFgukQORfcPJ2JAvjufS1Igw+560bY/aGwZbWGc2rZcMDcHpFGTdWlmjb6cQmArpD/QCN3uR5urD1MoJcs0vamuBbQG0DHDFgzTL+leNP7lGWdjiJvHJlsnHI0=; SC_DFP=yEBsjWhlzePmQwBNcvADwMCyPGGZddVj; _ga_SW6D8G0HXK=GS1.1.1728869718.5.1.1728869887.57.0.0; SPC_CDS=508c2bbc-edf1-4f4e-bc9e-cb243fbca0e1; SPC_CDS_CHAT=594316fc-f047-417e-b0f9-7a3ac2b26f60; _QPWSDCXHZQA=c7f71b1e-68fb-4886-bf8a-686021b6d91b; REC7iLP4Q=4bc29ff0-a4ac-45a2-b7d5-fcf6d43cb0f6; _sapid=5c1f0152f318c234fa865cd822428b249ce9410dda4ce095e03b7113; SPC_SI=pmWfZgAAAABucjFjUlRCeAgkpgMAAAAAd0Y1d29meHA=; SPC_SEC_SI=v1-dUNqOVRqdHpZdnNGUTJmN4jCHSAQfN3xuzxOjSy5xNxm8heVvc56WhuBucgho6uc3qw/UaGmOdtoyL9duNyrAAEjusFWg5DaR4SfBkEDPfo=; SPC_U=1334490526; CTOKEN=wJsqxovGEe%2Bcw9JVK2JlTA%3D%3D; shopee_webUnique_ccd=LPPGXNbcOK9SC3iYWDhnFg%3D%3D%7C3UeajCbAjH%2BlPmen%2BTnneZs9O%2BFLV%2BlvPBDwduHcWwuMt9HmGoYCwITNTSY%2FuBp%2BIN7%2B%2FIAw8KU%3D%7CI%2BRz226txnv2nkmJ%7C08%7C3; ds=ec58c4eafc7511cf68556dc22e218ea9',
        'user-agent':'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/125.0.0.0 Safari/537.36'
    }
    params = {
        #"extra_params": '{"global_search_session_id":"gs-48ba9e57-3cec-43a9-bfc0-a3fdcaad3995"}',
        "keyword": kw,
        "search_type": 0,
        "version": 1
    }
    ys_word_list=[kkk.lower() for kkk in yskw.split() if kkk.lower()!='t' and kkk.lower()!='tee']
    # 发起 GET 请求
    try:
        res = requests.get(url,headers=headers, params=params,timeout=10,
                           proxies=proxies
                           )

        # 检查响应状态码并处理
        if res.status_code == 200:
            json_data=res.json()
            with open('ResData/虾皮下拉.json','w',encoding='utf-8') as f:
                json.dump(json_data,f,ensure_ascii=False)
   
            kw_hascj=[]
            kdatas=json_data.get('keywords')
            aff_save=0
            for item in kdatas:
                cur_kw=item['keyword']
                if cur_kw.lower() not in kw_hascj:
                    kw_hascj.append(cur_kw.lower())
                    cur_word_list=[kkk.lower() for kkk in cur_kw.split() if kkk.lower()!='t' and kkk.lower()!='tee']
                    kw_num=len(cur_word_list)

                    if 'shirt' not in cur_kw.lower():
                        print(f'采集词《{cur_kw}》长度:{kw_num},不包含《shirt》,不保存')
                        continue

                    if kw_num<=ys_kw_num:
                        print(f'采集词《{cur_kw}》长度:{kw_num}小于等于原词《{yskw}》长度:{ys_kw_num},不保存')
                        continue

                    diff_word_set=(set(ys_word_list)-comman_kw_set)-(set(cur_word_list)-comman_kw_set)
                    if diff_word_set:
                        print(f'采集词《{cur_kw}》没有完全包含原始词《{yskw}》:{diff_word_set},不保存')
                        continue

                    cur_pos=item['position']
                    rs=ms.ExecQuery('select count(*) from KeyWord where KW=?',(cur_kw,))
                    if rs[0][0]==0:
                        aff_save+=ms.ExecNoQuery(f'insert into KeyWord(KW,searchFrom,WordNum,position) values(?,?,?,?)',[cur_kw,kw,kw_num,cur_pos])
                    else:
                        print(f'采集词《{cur_kw}》,已存在数据库,不保存')

            print(f'《{kw}》 共采集 {len(kdatas)} 个词,其中保存了{aff_save} 个')
            return aff_save
        else:
            print("请求失败! 状态码:", res.status_code)
            print("响应内容:", res.text)
    
    except Exception as e:

        print(f'《{kw}》 采集错误 =>{e}')
    time.sleep(1)

def main():
    xuns=1
    while True:
        st=time.time()
        rs=ms.ExecQuery('select id,kw,WordNum from KeyWord where state=0 and WordNum<=6')
        if len(rs)==0:
            break
        task_count=len(rs)
        suc=0
        j=1
        for kid,kw,knum in rs:
            for letter in list(string.ascii_lowercase)+['0','1','2','3','4','5','6','7','8','9']:

                cjkw=f'{kw} {letter}'
                res=req_kw(kw,cjkw,knum)
                if res:
                    suc+=res
            j+=1
            task_count-=1

            aff=ms.ExecNoQuery('update KeyWord set state=1 where id=?',(kid,))

            print(f'第{j}个,kid:{kid},关键词:《{kw}》,{knum}词,采集保存:{suc}个新词,更改状态:{aff},剩余:{task_count}\n\n\n')
        et=time.time()
        xuns+=1
        print(f'第一次循环结束,耗时:{et-st:.2f} 小时\n\n\n\n\n00')
        time.sleep(10)

if __name__=='__main__':

    main()